﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

using System.Data;

namespace PeekService
{

    public class ExtendService
    {
        /// <summary>
        /// 根据地区和类型获取所有分页
        /// </summary>
        /// <returns></returns>
        public List<TourInfoModel> GetAllByTypeRegion(int pageIndex, int pageSize, out int RecordCount, string typeCode, string RegionCode,string code,string title,string isPublish)
        {
//            string strsql = @"SELECT TourismInfo.Code, TourismInfo.Title, SysFile.FilePath, TourismInfo.[Desc], TourismInfo.Remark, 
//                      TourismInfo.RegionCode, TourismInfo.Tags, SysFile.ThumbnailsPath, SysFile.PreviewPath, SysFile.Remark AS picRemark, TourismInfo.IsEspecially,
//                    TourismInfo.Sort, TourismInfo.TypeCode FROM TourismInfo INNER JOIN SysFile ON TourismInfo.Code = SysFile.BusinessCode
//                    WHERE   (TourismInfo.RegionCode = '"+RegionCode+"') AND (TourismInfo.TypeCode = '"+typeCode+@"') ORDER BY
//                TourismInfo.Sort DESC";
            string whereStr = " where TourismInfo.State=0 ";
            if (!string.IsNullOrEmpty(typeCode))
            {
                whereStr += " AND (TourismInfo.TypeCode = '"+typeCode+"')";
            }
            if (!string.IsNullOrEmpty(RegionCode))
            {
                whereStr += " AND (CountriesRegion.RegionPath  like '" + RegionCode + "%')";
            }
            if (!string.IsNullOrEmpty(code))
            {
                whereStr += " AND (TourismInfo.Code ='" + code + "')";
            }
            if (!string.IsNullOrEmpty(title))
            {
                whereStr += " AND (TourismInfo.Title like '%" + title + "%')";
            }
            if (!string.IsNullOrEmpty(isPublish))
            {
                whereStr += " AND (TourismInfo.IsPublish ='" +isPublish + "')";
            }
            string strsql = @"SELECT TourismInfo.Code, TourismInfo.Title, TourismInfo.SubTitle,TourismInfo.TicketDan,TourismInfo.TicketWangFan,TourismInfo.SpecialRecommand, TourismInfo.[Desc], TourismInfo.Remark, 
                    TourismInfo.RegionCode, TourismInfo.Tags, TourismInfo.IsEspecially,TourismInfo.Sort, TourismInfo.TypeCode, TourismInfo.Hits,TourismInfo.IsPublish,
                    CountriesRegion.RegionPath FROM TourismInfo inner join CountriesRegion 
                    on TourismInfo.RegionCode=CountriesRegion.Code " + whereStr + " ORDER BY TourismInfo.IsEspecially desc,TourismInfo.Sort DESC";
           
            List<TourInfoModel> list = new List<TourInfoModel>();
            using (SqlDataReader dr = DBHelper.ExecuteReader(strsql, pageIndex, pageSize, out RecordCount))
            {
                while (dr.Read())
                {
                    TourInfoModel model = new TourInfoModel();

                    if (dr["Code"] != DBNull.Value)
                    {
                        model.Code = dr["Code"].ToString();
                    }
                    if (dr["Title"] != DBNull.Value)
                    {
                        model.Title = dr["Title"].ToString();
                    }
                    if (dr["SubTitle"] != DBNull.Value)
                    {
                        model.SubTitle = dr["SubTitle"].ToString();
                    }
                    if (dr["Desc"] != DBNull.Value)
                    {
                        model.Desc = dr["Desc"].ToString();
                    }
                    if (dr["Remark"] != DBNull.Value)
                    {
                        model.Remark = dr["Remark"].ToString();
                    }
                    if (dr["TicketDan"] != DBNull.Value)
                    {
                        model.TicketDan = dr["TicketDan"].ToString();
                    }
                    if (dr["TicketWangFan"] != DBNull.Value)
                    {
                        model.TicketWangFan = dr["TicketWangFan"].ToString();
                    }
                    if (dr["Tags"] != DBNull.Value)
                    {
                        model.Tags = dr["Tags"].ToString();
                    }
                    if (dr["IsEspecially"] != DBNull.Value)
                    {
                        model.IsSpecially = bool.Parse(dr["IsEspecially"].ToString());
                    }
                    else
                    {
                        model.IsSpecially = false;
                    }
                    //if (dr["ThumbnailsPath"] != DBNull.Value)
                    //{
                    //    model.ThumbnailPath = dr["ThumbnailsPath"].ToString();
                    //}
                    //if (dr["FilePath"] != DBNull.Value)
                    //{
                    //    model.FilePath = dr["FilePath"].ToString();
                    //}
                    //if (dr["PreviewPath"] != DBNull.Value)
                    //{
                    //    model.PreviewPath = dr["PreviewPath"].ToString();
                    //}
                    if (dr["TypeCode"] != DBNull.Value)
                    {
                        model.TypeCode = dr["TypeCode"].ToString();
                        model.TourType = new TourismInfoTypeService().GetModel(model.TypeCode).Name;
                    }
                    if (dr["Hits"] != DBNull.Value)
                    {
                        model.Hits = int.Parse(dr["Hits"].ToString());
                    }
                    if (dr["RegionPath"] != DBNull.Value)
                    {
                        model.Region = dr["RegionPath"].ToString().Replace(",", "-");
                    }
                    if (dr["IsPublish"] != DBNull.Value)
                    {
                        model.IsPublish = dr["IsPublish"].ToString();
                    }
                    if (dr["SpecialRecommand"] != DBNull.Value)
                    {
                        model.SpecialRecommand = dr["SpecialRecommand"].ToString();
                    }
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据景点ID获取所有图片方法
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<SysFile> GetFileByTourismCode(string code)
        {
            string strsql = @"select Code,RealName,FileName,FilePath,ThumbnailsName,ThumbnailsPath,PreviewName,PreviewPath,TourismCode,Remark,State,CreateID,CreateTime,Sort,IsCover from SysFile where TourismCode=@TourismCode  order by IsCover desc, sort desc";
            List<SysFile> list = new List<SysFile>();
            using (SqlDataReader dr = DBHelper.ExecuteReader(strsql, new SqlParameter("@TourismCode", code)))
            {
                while (dr.Read())
                {
                    list.Add(new SysFileService().DataRowToModel(dr));
                }               
            }
            return list;
        }
        /// <summary>
        /// 根据景点ID获取地图信息的方法
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public MapInfo GetMapByTourismCode(string code)
        {
            string strsql = @"select Code,TourismCode,Title,Content,Remark,State,CreateID,CreateTime,Sort from MapInfo where TourismCode=@TourismCode and state=0";
            MapInfo model = new MapInfo();
            using (SqlDataReader dr = DBHelper.ExecuteReader(strsql, new SqlParameter("@TourismCode", code)))
            {
                if (dr.Read())
                {
                    return new MapInfoService().DataRowToModel(dr);
                }
            }
            return null;
        }
        /// <summary>
        /// 根据景点ID获取景点票价信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<TicketInfo> GetTicketInfoByTourismCode(string code)
        {
            List<TicketInfo> list = new List<TicketInfo>();
            string sql = "select Code,TicketType,TourismCode,TicketFare,Seat,SoldSeat,PriceUnit,EffectiveDate,Currency,Remark,State,CreateID,CreateTime,Sort from TicketInfo where TourismCode=@TourismCode order by sort desc";
            using (SqlDataReader dr = DBHelper.ExecuteReader(sql, new SqlParameter("@TourismCode", code)))
            {
                while (dr.Read())
                {
                    list.Add(new TicketInfoService().DataRowToModel(dr));
                }
            }
            return list;
        
        }
        /// <summary>
        /// 根据表名字计算出最大的排序值
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public int GetSort(string tableName) {
            string sql = "SELECT MAX(SORT) FROM "+tableName;            
            object obj = DBHelper.ExecuteScalar(sql);
            int sort=0;
            if (obj != null)
            {
                if (int.TryParse(obj.ToString(), out sort))
                {
                    sort = sort + 1;
                }
            }
            else
            {
                sort = 1;
            }
            return sort;
        }
        /// <summary>
        /// 根据关键字查询景点列表
        /// </summary>
        /// <param name="keywords">关键词</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="recordCount">宗记录数</param>
        /// <returns></returns>
        public List<TourismInfo> Search(string keywords,int pageIndex,int pageSize,out int recordCount,string typeCode)
        {
            List<TourismInfo> list = new List<TourismInfo>();        
            string sql = "SELECT * FROM TOURISMINFO WHERE ((TITLE LIKE '%"+keywords+"%') or ([Desc] like '%"+keywords+"%') or (Remark like '%"+keywords+"%'))";
            if (!string.IsNullOrEmpty(typeCode))
            {
                sql += " AND TypeCode='" + typeCode + "'";
            }
            using (SqlDataReader reader = DBHelper.ExecuteReader(sql, pageIndex, pageSize, out recordCount))
            {
                while (reader.Read())
                {
                    list.Add(new TourismInfoService().DataRowToModel(reader));
                }
            }
            return list;

        }
        /// <summary>
        /// 根据关键字查找类型个数的方法
        /// </summary>
        /// <param name="keywords"></param>
        /// <returns></returns>
        public List<int> GetCountByKeywords(string keywords)
        {
            string sql = "";
            if (!string.IsNullOrEmpty(keywords))
            {
                sql = @"select COUNT(TourismInfo.Code) geshu from (select * from (select * from  TourismInfo  WHERE TITLE LIKE 
                                   '%"+keywords+@"%' or [Desc] like '%"+keywords+@"%' or Remark like '%"+keywords+@"%')t) TourismInfo right join TourismInfoType TourismInfoType on
 TourismInfo.TypeCode=TourismInfoType.Code  group by TourismInfoType.Code,TourismInfoType.Name ,TourismInfoType.Sort order by  TourismInfoType.IsEspecially desc, TourismInfoType.Sort desc";
            }
            else
            {
                sql = @"select COUNT(TourismInfo.Code) geshu,TourismInfoType.Name from TourismInfo right join TourismInfoType TourismInfoType on
 TourismInfo    .TypeCode=TourismInfoType.Code  group by TourismInfoType.Code,TourismInfoType.Name ,TourismInfoType.Sort order by TourismInfoType.IsEspecially desc, TourismInfoType.Sort desc";

            }
            List<int> list = new List<int>();
            using (SqlDataReader reader = DBHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetInt32(0));
                }
                if (!reader.IsClosed)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }
            return list;
        }
        /// <summary>
        /// 在表中查出是否在表中是否存在改值,返回值true：不存在，可以添加，false 存在，不可以添加该值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="columnValue">列的值</param>
        /// <returns>true：不存在，可以添加，false 存在，不可以添加该值</returns>
        public string CheckExistByOneColumn(string tableName, string columnName, string columnValue)
        {
            string sql = "SELECT COUNT(CODE) FROM " + tableName + " WHERE " + columnName + "='" + columnValue + "' and state=0";
            object obj = DBHelper.ExecuteScalar(sql);
            if (obj != null)
            {
                return int.Parse(obj.ToString()) == 1 ? "false" : "true";
            }
            return "false";
        }
        /// <summary>
        /// 根据Code修改排序方法
        /// </summary>
        /// <param name="tableName">需要排序的表名</param>
        /// <param name="code">当前操作的Code</param>
        /// <param name="up">true Sort增大,false：Sort减小</param>
        public void SetOrder(string tableName, string code, bool up)
        {
            try
            {
                string sql = "";
                if (up)
                {
                    sql = @"declare @sort int;
                        declare @sort2 int;
                        declare @code varchar(36);
                        select @sort=Sort from " + tableName + " where Code='" + code + @"'
                        if exists(select * from "+tableName+@" where  Sort>@sort and state=0)
                        begin
                        select @sort2=Sort,@code=Code  from " + tableName + @" where Code=(select top 1 Code from " + tableName + @" where  Sort>@sort and state=0 order by Sort)
                        update " + tableName + @" set Sort=@sort2 where Code='" + code + @"'
                        update " + tableName + @" set Sort=@sort where Code= @code 
                        end";
                }
                else
                {
                    sql = @"declare @sort int;
                        declare @sort2 int;
                        declare @code varchar(36);
                        select @sort=Sort from " + tableName + " where Code='" + code + @"'
                        if exists(select * from " + tableName + @" where  Sort<@sort and state=0)
                        begin
                        select @sort2=Sort,@code=Code  from " + tableName + @" where Code=(select top 1 Code from " + tableName + @" where  Sort<@sort and state=0 order by Sort desc)
                        update " + tableName + @" set Sort=@sort2 where Code='" + code + @"'
                        update " + tableName + @" set Sort=@sort where Code= @code 
                        end";
                }
                DBHelper.ExecuteNonQuery(sql);
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取国家和地区的方法
        /// </summary>
        /// <param name="destination">为空查找国家，否则查找国家为Code的province或者city</param>
        /// <param name="level">如果是2，根据国家code查找省、code为3则查找市</param>
        /// <returns></returns>
        public List<string> GetCountryRegion(string destination, int level)
        {
            List<string> list = new List<string>();
            string sql = "select Country from CountriesRegion  where state=0  order by sort desc   ";
            if (!string.IsNullOrEmpty(destination))
            {
                if (level == 2)
                {
                    sql = "select province from CountriesRegion where  state=0  and country='" + destination + "'  order by sort desc ";
                }
                else
                {
                    sql = "select City from CountriesRegion where state=0  and  Province='" + destination + "'  order by sort desc ";
                }
            }
            using (SqlDataReader reader = DBHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    if (reader[0] != DBNull.Value)
                    {
                        if(string.IsNullOrEmpty(list.Find(o=>o.Equals(reader[0].ToString()))))//如果没有就添加
                        list.Add(reader.GetString(0));
                    }
                }              
            }
            return list;
        }

        /// <summary>
        /// 根据景点ID删除相应的数据
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public bool DeleteFileByTourismCode(string tablename,string code)
        {
            string sql = "delete " + tablename + " where TourismCode=@TourismCode";
            SqlParameter[] parameter = { new SqlParameter("@TourismCode", SqlDbType.VarChar, 36) };                
            parameter[0].Value =code ; 
           
            int rows = DBHelper.ExecuteNonQuery(sql, parameter);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获取Banner图方法
        /// </summary>
        /// <returns></returns>
        public List<BannersModel> GetBanners() {
            string strsql = @"select Title,BannerPath,Content,Remark from Banners where state=0 order by sort desc"; 
            List<BannersModel> list = new List<BannersModel>();
            using (SqlDataReader reader = DBHelper.ExecuteReader(strsql))
            {
                while (reader.Read())
                {
                    BannersModel model = new BannersModel();
                    if (reader["Title"] != DBNull.Value)
                    {
                        model.Title = reader["Title"].ToString();
                    }
                    if (reader["BannerPath"] != DBNull.Value)
                    {
                        model.BannerPath = reader["BannerPath"].ToString();
                    }
                    if (reader["Content"] != DBNull.Value)
                    {
                        model.Content = reader["Content"].ToString();
                    }
                    if (reader["Remark"] != DBNull.Value)
                    {
                        model.Des = reader["Remark"].ToString();
                    }
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 获取推荐列表方法
        /// </summary>
        /// <param name="typeCode"></param>
        /// <param name="RegionCode"></param>
        /// <returns></returns>
        public List<RecommendModel> GetRecomendList(string typeCode, string RegionCode,bool isEspecially,int pageIndex,int pageSize,out int recordCount)
        {
            string whereStr = " where TourismInfo.State=0  and SysFile.IsCover=1 and IsPublish='是'";
            if (isEspecially)
            {
                whereStr += " and TourismInfo.IsEspecially=1 ";
            }
            if (!string.IsNullOrEmpty(typeCode))
            {
                whereStr += " AND (TourismInfo.TypeCode = '" + typeCode + "')";
            }
            if (!string.IsNullOrEmpty(RegionCode))
            {
                whereStr += " AND (TourismInfo.RegionCode ='" + RegionCode + "')";
            }
            string strsql = @"SELECT TourismInfo.Code, TourismInfo.Title,TourismInfo.SubTitle,TourismInfo.[Desc],
                    TourismInfo.IsEspecially,TourismInfo.Sort, TourismInfo.TypeCode,TourismInfo.Remark,
                    CountriesRegion.RegionPath ,SysFile.ThumbnailsPath, SysFile.PreviewPath FROM TourismInfo inner join CountriesRegion 
                    on TourismInfo.RegionCode=CountriesRegion.Code inner join SysFile on SysFile.TourismCode=TourismInfo.Code " + whereStr + " ORDER BY TourismInfo.Sort DESC";

            List<RecommendModel> list = new List<RecommendModel>();
            SqlDataReader dr = null;
            if (isEspecially)
            {
                dr = DBHelper.ExecuteReader(strsql);
                recordCount = 100;
            }
            else
            {
                dr = DBHelper.ExecuteReader(strsql, pageIndex, pageSize, out recordCount);
            }
           
            while (dr.Read())
            {
                RecommendModel model = new RecommendModel();
                if (dr["Code"] != DBNull.Value)
                {
                    model.Id = dr["Code"].ToString();
                }
                if (dr["Title"] != DBNull.Value)
                {
                    model.Title = dr["Title"].ToString();
                }
                if (dr["SubTitle"] != DBNull.Value)
                {
                    model.SubTitle = dr["SubTitle"].ToString();
                }
                //if (dr["Desc"] != DBNull.Value)
                //{
                //    model.Des = dr["Desc"].ToString();
                //}
                //if (dr["Remark"] != DBNull.Value)
                //{
                //    model.Remark = dr["Remark"].ToString();
                //}
                if (dr["ThumbnailsPath"] != DBNull.Value)
                {
                    model.ThumbnailPath = dr["ThumbnailsPath"].ToString();
                }
                if (dr["PreviewPath"] != DBNull.Value)
                {
                    model.PreviewPath = dr["PreviewPath"].ToString();
                }
                list.Add(model);
            }
            if (!dr.IsClosed)
            {
                dr.Close();
                dr.Dispose();
            }
           
            return list;
        }

        /// <summary>
        /// 获取友情链接列表
        /// </summary>
        /// <returns></returns>
        public List<string> GetLinks()
        {
            string sql = "SELECT [Content] FROM LINKS WHERE STATE=0 ORDER BY SORT DESC";
            List<string> list = new List<string>();
            using (SqlDataReader dr = DBHelper.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    list.Add(dr.GetString(0));
                }                
            }
            return list;
        }
        /// <summary>
        /// 根据类型ID查找
        /// </summary>
        /// <param name="typeCode"></param>
        /// <returns></returns>
        public List<object> GetTourCount(string typeCode,string regionCode)
        {
            List<object> list = new List<object>();
            string whereStr = "where TourismInfo.State=0 and TourismInfoType.State=0 and TourismInfo.IsPublish='是' and CountriesRegion.State=0 ";
            if (!string.IsNullOrEmpty(typeCode))
            {
                whereStr += " and  TourismInfoType.Code='" + typeCode + "'";
            }
            if (!string.IsNullOrEmpty(regionCode))
            {
                whereStr += " and TourismInfo.RegionCode='"+regionCode+"'";
            }
            string sql = @"SELECT count(dbo.TourismInfo.Code)c, dbo.TourismInfoType.Name,TourismInfoType.Icon,TourismInfoType.Code FROM dbo.TourismInfo INNER JOIN
                      dbo.TourismInfoType ON dbo.TourismInfo.TypeCode = dbo.TourismInfoType.Code INNER JOIN
                      dbo.CountriesRegion ON dbo.TourismInfo.RegionCode = dbo.CountriesRegion.Code " + whereStr + " group by TourismInfoType.Name,TourismInfoType.Code,TourismInfoType.Icon";
            using (SqlDataReader reader = DBHelper.ExecuteReader(sql))
            {
                if (reader.Read())
                {
                    list.Add(new { Count = reader.GetInt32(0), TypeName = reader.GetString(1), Icon = reader.GetString(2), Code = reader.GetString(3) });
                }
            }
            return list;
        }

        /// <summary>
        /// 更新点击次数
        /// </summary>
        /// <param name="code"></param>
        public void UpdateHits(string code) {
            string sql = "update TourismInfo set hits=hits+1 where code='"+code+"'";
            DBHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 删除数据的方法
        /// </summary>
        /// <param name="ids">ids字符串</param>
        /// <param name="tableName">表名字</param>
        /// <param name="isDelete">true为物理删除，false为逻辑删除</param>
        /// <returns></returns>
        public bool DeleteAll(string ids, string tableName, bool isDelete)
        {
            try
            {
                string sql = "";
                if (isDelete)
                {
                    sql = "Delete " + tableName + " where Code in (" + ids + ")";
                }
                else
                {
                    sql = "update " + tableName + " set State=1 where Code in (" + ids + ")";
                }
                if (DBHelper.ExecuteNonQuery(sql) > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 发布景点信息方法
        /// </summary>
        /// <param name="ids">id字符串</param>
        /// <param name="isPublish">true为发布，false为不发布</param>
        /// <returns></returns>
        public bool PublishTourInfo(string ids, bool isPublish)
        {
            try
            {
                string sql = "";
                if (isPublish)
                {
                    sql = "update  TourismInfo set IsPublish='是' where Code in (" + ids + ")";
                }
                else
                {
                    sql = "update TourismInfo set IsPublish='否'  where Code in (" + ids + ")";
                }
                if (DBHelper.ExecuteNonQuery(sql) > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 获取国家地区
        /// </summary>
        /// <returns></returns>
        public List<string> GetCountryRegion() {
            List<string> list = new List<string>();
            string sql = "SELECT DISTINCT Country,Province,SORT FROM CountriesRegion WHERE STATE=0 ORDER BY SORT DESC,Country";
            using (SqlDataReader reader = DBHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    if (reader["Country"] != DBNull.Value && reader["Province"] != DBNull.Value)
                    {
                        if (!list.Contains(reader["Country"] + "," + reader["Province"]))
                        list.Add(reader["Country"] + "," + reader["Province"]);
                    }
                }
            }
            return list;
        }
    }

}
